如何利用复合索引解决性能问题
这个案例发生在某天早上，运行在配置为128GB内存、64CPU的HP Superdome上的Oracle 9.2.0.8，出现CPU占用将近100%，运行队列达到60~80，应用反应速度很慢的异常情况。
在用户反映速度很慢后，检查Oracle，发现很多的会话在等待latch free，latch#为98：
SQL> select * fromv$latchname where latch#=98;
LATCH# NAME
---------- ----------------------------------------------------------------
98 cache buffers chains
由于本章重点描述的是索引，关于“cache buffers chains latch”的等待，此处不做过多说明，这个latch的等待，通常情况下表明存在热点块，一般都是由于没有正确使用索引、SQL所使用的索引选择率不高引起。检查正在等待latch free的会话正在执行的SQL，大部分都在执行类似于下面的SQL：
SELECT SUM(cnt),
to_char(nvl(SUM(nvl(amount, 0)) /100, 0), ’FM9999999999990.90′) amount
FROM (select count(payment_id) cnt,SUM(amount) amount
from TABLE_A
where staff_id = 12345
and CREATED_DATE >= trunc(sysdate)
and state = ’C0C’
and operation_type in (’5KA’,’5KB’, ’5KC’, ’5KP’))
这里对SQL做了一些处理。
看起来这个SQL并不复杂，查看其执行计划：
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost |Pstart |Pstop |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 125K | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | |
| 2 | VIEW | | 1 | 26 | 125K | | |
| 3 | SORT AGGREGATE | | 1 | 30 | | | |
|* 4 | TABLEACCESS BY GLOBAL INDEX ROWID |TABLE_ | 19675 | 576K | 125K | ROWID |ROW L |
|* 5 | INDEX RANGE SCAN | IDX_A_3 | 1062K | | 3919 | | |
-------------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
4 - filter(”TABLE_A”.”STAFF_ID”=12345 AND”TABLE_A”.”STATE”=’C0C’ AND
(”TABLE_A”.”OPERATION_TYPE”=’5KA’ OR”TABLE_A”.”OPERATION_TYPE”=’5KB’
OR ”TABLE_A”.”OPERATION_TYPE”=’5KC’ OR”TABLE_A”.”OPERATION_TYPE”=’5KP’))
5 -access(”TABLE_A”.”CREATED_DATE”>=TRUNC(SYSDATE@!))
Note: cpu costing is off
从中可以看到，Oracle评估出，利用索引扫描返回的行数高达100万行，可想而知，由于选择率过高，产生了大量的buffers chains latch争用。
检查PAYMENT表的索引：
SQL> select index_name,index_type from user_indexeswhere table_name=’TABLE_A’;
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
IDX_A_1 NORMAL
IDX_A_2 NORMAL
IDX_A_3 NORMAL
IDX_A_4 NORMAL
IDX_A_5 NORMAL
IDX_A_6 NORMAL
IDX_A_7 NORMAL
IDX_A_8 NORMAL
PK_TABLE_A NORMAL
SQL> selectindex_name,column_name,column_position from user_ind_columns where table_name=’TABLE_A’order by 1,3;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------
IDX_A_1 SERIAL_NBR 1
IDX_A_2 A_ID 1
IDX_A_3 CREATED_DATE 1
IDX_A_4 METHOD 1
IDX_A_5 P_METHOD 1
IDX_A_6 S_ID 1
IDX_A_7 STAFF_ID 1
IDX_A_7 STATE_DATE 2
PK_TABLE_A TABLE_A_ID 1
以上输出是对真正的输出信息加工处理后的结果。
由上可知，执行计划中使用的索引IDX_A_3是在CREATED_DATE列上建立的单列索引。
这个SQL在之前没有出现过类似问题，那问题在哪里？原来在当天凌晨做了一个大数量的业务操作，在TABLE_A中插入了大量的数据，因此用CREATED_DATE>=TRUNCATE(SYSDATE)这个条件时会从索引扫描中返回大量的行。而实际上回表之后用STAFF_ID和OPERATION_TYPE列上的条件过滤后的行数仅约2万行（这是评估的数据，实际的数据远远比这个少）。很显然，如果我们建立一个复合索引，那么索引扫描返回的行数将大大减少，这样也就大大减少了在表上访问并进行过滤的数据量。
以STAFF_ID列为前导列与CREATE_DATE列一起建立复合索引后，系统马上恢复正常。不过，有人会问，为什么要使用STAFF_ID列做索引的前导列，而不用CREATE_DATE列做前导列？很多文档不是介绍说，复合索引要把选择性最好的列放在最前面吗？要回答这个问题，得首先了解索引的基本原理，包括Oracle数据库对索引是如何存储的、是怎样通过索引来检索索引数据的，等等。
B Tree索引的结构及特点
Oracle数据库中索引的存储结构使用的是B Tree的一种变体，称为B*Tree（B Star Tree），在数据库中存储数据以块为单位，索引也不例外，数据库中构建索引形成的BTree，与教科书中提到的B Tree有很明显的差异。下面以图11-1为例，介绍Oracle数据库中B Tree索引的结构及其特点。
图11-1 Oracle数据库中B Tree索引的结构及其特点示意图
图11-1是一个简单的B Tree索引示意图，图中虚线部分表示省略的部分。在介绍B Tree索引的特点之前，我们先来回顾一下数据结构中树的几个术语。
节点M的深度：从树根节点到节点M的最短路径长度。图中根节点Root的深度为0，节点L1-1的深度为1，节点L0-1的深度为2。
节点M的层数：节点M的层数与其深度，实际上是相同的。
树的高度：树的深度值最大的那个节点，其深度+1即为树的高度。比如图中树的高度为3。
Oracle数据库的索引，有以下几个特点：
存储索引数据的块，称为B Tree树的节点。有三种类型的节点，根（Root）节点、分枝（Branch）节点和叶（Leaf）节点。高度为1的索引，只有根节点，这个时候，索引只有唯一的一个叶节点，也同时是根节点，高度大于1时，根节点与分枝节点具有完全相同的结构，也就是说，这个时候的根节点，实际上也是一种分枝节点。分枝节点索引块存储的数据主要包括：索引值、键值对应的下一级节点块地址、还有一个称之为“kdxbrlmc”的指针，也就是如图所示的“lmc”，这个指针就是比当前枝节点中最小的索引值还小的下一级节点块的数据块地址（DBA）。而叶节点索引块存储的数据主要是索引值以及对应的ROWID，和当前节点的前后两个节点的数据块地址。
索引的根节点，总是紧接在索引段头的后面的一个数据块。比如某个索引的段头为7/7817（相对文件号/块号），那么根节点块就是7/7818。这个特点，是很少有文档提及的，但是这个小小的特点，其实非常重要。Oracle执行SQL时，直接从数据字典得到段头位置后就能定位到根节点。这个特性使得就算是在小表上，使用索引也能减少逻辑读，对于频繁访问的索引，特别是以INDEX UNIQUE SCAN方式访问索引，所节省的逻辑读是非常多的。下面我们做一个简单的测试，测试的数据库版本为Linux AS4上的Oracle 10.2.0.4：
--创建一个只有2列、4行的表：
SQL> create tablet1 as select object_id,object_name from dba_objects where rownum<=4;
Table created.
--创建一个非唯一索引：
SQL> create indext1_idx1 on t1(object_id);
Index created.
SQL> set autot onstat
SQL> colobject_name for a30
--全表扫描（Table Full Scan）：
SQL> select /*+full(t1) */ * from t1 where object_id=28;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
28 CON$
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--索引范围扫描（Index Range Scan）：
SQL> select /*+index(t1) */ * from t1 where object_id=28;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
28 CON$
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotoff
--删除索引，重新创建一个唯一索引：
SQL> drop indext1_idx1;
Index dropped.
SQL> createunique index t1_idx1 on t1(object_id);
Index created.
--索引唯一扫描（Index Unique Scan）：
SQL> set autot onstat
SQL> select /*+ index(t1) */ * from t1 whereobject_id=28;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
28 CON$
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在上面的测试中，创建了一个只有2列，4行的表，这个表只占用了1个数据块的空间。对同样的SQL，全表扫描、索引范围扫描、索引唯一扫描3种不同的访问方式，其逻辑读各不相同：
注意在实际的测试中，每一个SQL应至少执行两次，并以最后一次SQL执行后的逻辑读等统计数据为准，因为在SQL解析时有递归调用，产生了其他的逻辑读。
从上面的测试可以看到，对即使是很小的表，如果返回的数据量很小，使用索引都能够减少逻辑读，从而具有更好的性能。
索引是始终保持平衡的。这里所说的平衡是指索引高度是保持平衡的，也就是从根节点到任意一个叶节点，其路径都是等距的。比如图11-1中，从“Root”到叶节点“L0-1”与“Root”到叶节点“L0-5”，都要访问3个块。可以说，这是B Tree索引最重要的一个特性。值得注意的是，在有的书和文章上面，提到B Tree索引不平衡，是指索引中的数据是倾斜的。如果某一个表删除了大量的数据，会形成索引中很多的块，只有很少量的数据甚至是空块。比如图11-1中，叶节点“L0-2”只有1条数据。这种情况常见于单向增长的列上的索引，比如Sequence、日期类型等，在删除了大量数据后，由于列是单向增长的，除非是空块，否则剩余空间很难得到重用。
索引的每一个叶节点，有两个指针，分别指向比当前节点最小索引值还小的叶节点块地址，以及比当前节点最大索引值还大的叶节点块地址。通过这两个指针，把所有的叶节点串起来，形成一个双向链表。在这个双向链表上的所有索引值，从小到大排列，而对于倒序（Desc）索引，则是从大到小排列。值得注意的是，对于非唯一索引来说，每个值所对应的ROWID，也是索引值的一部分，所以在组成索引的各个列值均相等的情况下，会按ROWID为顺序进行排序。
索引的分枝节点块所存储的索引值，并不是完整的索引值，而只是整个索引值的前缀，只要能够区分其大小就可以了。比如在前面的索引示意图11-1中的“L1-1”分枝节点，有两个值，AD和AK，其指向的叶节点起始索引值为ADK以及AKA，但是其前缀AD和AK即可以区分其大小。这种设计，能够使分枝节点存储更多的条目，减少了分枝节点数，特别是在多列复合索引中，对于很大的表，甚至可以减少B Tree树的高度。下面我们做个简单的测试：
SQL> create tablet1 as select lpad(rownum,200,'0') id1,rpad(rownum,200,'0')id2,object_id,object_name from dba_objects;
Table created.
SQL> create index t1_idx1 on t1(id1,object_name);
Index created.
SQL> create indext1_idx2 on t1(id2,object_name);
Index created.
SQL> execdbms_stats.gather_table_stats(user,'T1',cascade=>true);
PL/SQL proceduresuccessfully completed.
SQL> selectindex_name,blevel,leaf_blocks,distinct_keys,
2 num_rowsfrom user_indexes where table_name='T1' order by 1;
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS
------------------------------ ---------- ----------- ------------- ----------
T1_IDX1 3 1527 45354 45354
T1_IDX2 2 1526 45354 45354
从测试结果可以看到，在同一个表中，对长度相同的列，所建的索引，其分枝节点数也是大不相同的，导致其索引高度也不一样。使用alter session set events‘immediatetrace name treedump level index_object_id’命令，可以更清楚地从产生的trace文件看到索引的树结构。在这条命令中，index_object_id是指user_objects中查出来的索引名称对应object_id。
B Tree索引不对NULL值进行索引，对于某一行，索引的所有列的值都是NULL值时，该行不能被索引。不过Cluster Index是可以对NULL值进行索引的，但是本文主要是讨论普通表上的B Tree索引，对Cluster Index不做讨论。由于Oracle索引的这个特性，使得IS NULL这种条件的SQL不能够使用索引。但是我们可以通过建复合索引的形式来使这种SQL也能够使用索引，下面是一个简单的测试：
Connected to:
Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
SQL> create tablet1 as select * from dba_objects;
Table created.
SQL> create indext1_idx1 on t1(object_id);
Index created.
SQL> execdbms_stats.gather_table_stats(user,'t1',cascade=>true);
PL/SQL proceduresuccessfully completed.
SQL> explain planfor select * from t1_idx1 where object_id is null;
SQL> explain planfor select * from t1 where object_id is null;
Explained.
SQL> select *from table(dbms_xplan.display);
----------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 93 | 172 (2) | 00:00:03 |
|* 1 | TABLE ACCESS FULL | T1 | 1 | 93 | 172 (2) | 00:00:03 |
----------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID" IS NULL)
SQL> create index t1_idx2 on t1(object_id,1)compute statistics;
Index created.
SQL> explain planfor select * from t1 where object_id is null;
Explained.
SQL> select *from table(dbms_xplan.display);
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 93 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 93 | 1 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN |T1_IDX2 | 1 | | 1 (0) | 00:00:01 |
---------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2- access("OBJECT_ID" IS NULL)
在上面的测试中，建立了(object_id,1)这样一个复合索引，而常量“1”的存在，将会使表中的所有行都能够进行索引，这样使得如“select * from t1_idx1 where object_id is null”这样的SQL也能够使用索引，这在NULL值非常少时，能够大幅提升SQL的性能。
Oracle如何扫描索引
下面以图11-1所示的升序索引为例，描述Oracle扫描索引的工作方式。深入理解Oracle索引扫描，对于使用索引时的成本有更进一步的了解，以便我们能够选择正确的索引来优化性能，解决性能问题。
Oracle在扫描B Tree索引时，根据扫描的方向有两种，一种是从索引的左端扫描到右端，另一种就是相反的方向，从索引的右端扫描到左端。这里所谓的左端右端指的是图11-1中索引左边的块和右边的块。
对于索引唯一扫描来说，因为从索引最多只返回1行结果，所以从左端扫描到右端，实际上没有区别。二者的区别主要体现在索引范围扫描上，包括索引跳跃扫描（INDEX SKIP SCAN）和索引全扫描（INDEX FULL SCAN）。
一般情况下，索引都是从左端扫描到右端，只有在使用了如INDEX_DESC这样的HINT或使用ORER BY DESC（如果是降序索引，则是ORDER BY）时，才会从右端扫描到左端。
Oracle在扫描索引时，首先从数据字典中得到索引段头块地址，这个块地址+1即得到索引Root Block块地址。通过Root Block定位到Branch Block，再通过Branch Block定位到下一级Branch Block，直至最后定位到Leaf Block。然后从定位到的Leaf Block根据确定的扫描方向，从左向右扫描或从右向左扫描。
在从索引中扫描到数据，包括ROWID之后，如果所获得的数据已经满足需要，则将数据返回给上一步，比如直接返回给客户端，否则须根据ROWID，再从表里面获得数据返回给上一步。
下面结合前面的示意图11-1，以下面的SQL为例，演示Oracle是如何扫描索引的。
SELECT NAME FROM T1WHERE NAME BETWEEN ‘BDF’ AND ‘BOO’
从Root Block开始扫描，由于'BDF'>='B'，因此，定位的下一级Block为L1-2。
在L1-2这个枝节叶块（Branch Block）中，由于'BDF'小于块中最小值’BI’，因此定位的下一级Block为“lmc”指向的Block'L0-4'。
L0-4为叶节点块（LeafBlock），因此不需要定位到下一级块。在这个块中，可直接找到需要的数据'BDF'，然后一直读到块的最后一行数据'BGG'。由于'BGG'小于查询条件中的'BOO'，因此还需要扫描下一个叶节点块L0-5。
L0-5这个块，其最大值'BMW'仍然小于'BOO'，因此仍然需要扫描一个叶节点块L0-6。
在L0-6这个块中，紧接着'BOO'值之后的'BRZ'，已经大于查询条件中的值'BOO'，因此索引扫描到这个块时结束。
常见的几种索引扫描方式包括：
INDEX UNIQUE SCAN：索引唯一扫描，出现在唯一索引的“所有列”上使用“等于”条件的SQL中，最多只返回一行结果。
INDEX RANGE SCAN：索引范围扫描，出现在非唯一索引的前缀列或所有列上使用任意比较条件的SQL中。
INDEX FULL SCAN：索引全扫描，按顺序扫描索引中的所有叶节点块。
INDEX FAST FULL SCAN：索引快速全扫描，类似于全表扫描，扫描索引段中高水位下所有的块。
INDEX SKIP SCAN：索引跳跃扫描，出现在复合索引中，对非前缀列上使用任意比较条件的SQL中。Oracle在扫描索引时，会扫描所有的Branch Block，然后对Branch Block中的每一个值，都会去扫描Leaf Block。这适合于复合索引的前缀列不同值个数很少的情况，否则其扫描成本非常高。
在上面5种扫描方式中，除了第4种，都是对索引进行单块读，因此在进行这样的扫描时，经常可以看到“db file sequential read”等待事件，而第4种是INDEX FAST FULL SCAN，类似于全表扫描，进行多块读，在这种方式下通常会出现“db file scatter read”等待。同时，在上面所列举扫描的方式中，1~3这三种扫描方式，其结果是按值的大小顺序返回的，因此在需要顺序结果时，比如ORDERBY，能够避免在这些列上进行排序。
深入理解Oracle扫描索引时的工作过程，有时会有助于我们采用一些巧妙的方法，来解决SQL的性能问题，下期我们将分享一个这样的案例：巧用索引降序扫描解决性能问题。
文章来源：《Oracle DBA手记1》第03章 合理利用索引解决性能问题
点击原文链接可下载本书前两章内容。
配图来源：http://blog.csdn.net/sharqueen_wu/article/details/8787803